"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : GSC功能模块
Case Name   : gsc打开，线程池模式，某个库中某张表未进行缓存/已缓场景下，验证该库相应系统缓存信息变化
Create At   : 2022/4/27
Owner       : @peilinqian
Description :
    1、打开线程池、gsc开关;
    2、重启数据库，使参数生效;
    3、创建数据库;
    4、创建10个表;
    5、查询所有库并进行连接;
    6、表未缓存时，查看syscache占用情况;
    7、表已缓存时，查看syscache占用情况;
Expect      :
    1、打开线程池、gsc开关; 成功
    2、重启数据库，使参数生效; 重启成功
    3、创建数据库; 创建成功
    4、创建10个表; 创建成功
    5、查询所有库并进行连接; 连接成功
    6、表未缓存时，查看syscache占用情况; 系统缓存增加，包含已缓存的用户表
    7、表已缓存时，查看syscache占用情况; 系统缓存不增加或者增加，增加系统缓存信息不含已缓存的用户表
History     :
    modified: 2023/2/7 modified by @peilinqian,验证某张表已缓存的情况下，系统缓存增加导致断言失败。
    原因：后台会自动进行一些系统表、视图的查询操作，导致数据库的系统缓存增加。
    优化用例，若系统缓存增加，比较上次保存的缓存信息，判断缓存信息增加且不包含已经缓存的用户表。
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.db_name = 'db_gsc0018'
        self.tb_num = 10
        self.tb_ex_name = 'tb_gsc0018_'
        self.tb_tmp_catalog = 'tb_gsc0018_tmp_catalog'
        self.tb_tmp_table = 'tb_gsc0018_tmp_table'
        self.init_para1 = self.com.show_param("enable_global_syscache")
        self.init_para2 = self.com.show_param('enable_thread_pool')

    def test_main(self):
        if self.init_para1 == "on" and self.init_para2 == "on":
            step_txt = '----线程池、gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False
        else:
            step_txt = '----step1:打开线程池、gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = self.sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)
            result = self.sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_thread_pool= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = self.sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True

        step_txt = f'----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'create database {self.db_name};'
        create_result = self.sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                      create_result, '执行失败:' + step_txt)

        step_txt = f'----step4:创建{self.tb_num}个表; expect:创建成功----'
        self.log.info(step_txt)
        for i in range(self.tb_num):
            create_tb_sql = f"create table {self.tb_ex_name}{i} " \
                f"(c_integer integer,c_tinyint tinyint," \
                f"c_smallint smallint,c_binary_integer binary_integer," \
                f"c_bigint bigint,c_numeric numeric(10,4)," \
                f"c_number number(10,4),c_smallserial smallserial)"
            create_result = self.sh.execut_db_sql(create_tb_sql,
                                                  dbname=f'{self.db_name}')
            self.log.info(create_result)
            self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                          '执行失败:' + step_txt)

        step_txt = '----step5:创建中间表，记录gs_gsc_catalog_detail()\
        gs_gsc_table_detail()缓存信息; expect:创建成功----'
        self.log.info(step_txt)
        create_tmp_sql = f"create table {self.tb_tmp_catalog} as " \
            f"select database_id,database_name,rel_id,rel_name," \
            f"cache_id,self,infomask,infomask2,hash_value," \
            f"refcount from gs_gsc_catalog_detail() " \
            f"where database_name = '{self.db_name}';" \
            f"select * from {self.tb_tmp_catalog} " \
            f"where rel_name ='{self.tb_tmp_catalog}';" \
            f"create table {self.tb_tmp_table} as " \
            f"select * from gs_gsc_table_detail() " \
            f"where database_name = '{self.db_name}';" \
            f"select * from {self.tb_tmp_table} " \
            f"where relname ='{self.tb_tmp_table}';"
        tmp_msg = self.sh.execut_db_sql(create_tmp_sql,
                                        dbname=f'{self.db_name}')
        self.log.info(tmp_msg)
        self.assertEqual(2, tmp_msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + step_txt)

        step_txt = '----step6:表未缓存时，查看syscache占用情况; ' \
                   'expect:系统缓存增加，包含已缓存的用户表----'
        self.log.info(step_txt)
        gsc_size2 = 0
        for i in range(self.tb_num):
            self.save_tmp_info()
            check_sql = f"select * from {self.tb_ex_name}{i};" \
                f"select * from gs_gsc_dbstat_info() where " \
                f"database_name = '{self.db_name}';" \
                f"select usedsize from gs_gsc_memory_detail " \
                f"where db_id = (select cast((select oid from " \
                f"pg_database where datname = '{self.db_name}') as text));"
            check_result = self.sh.execut_db_sql(check_sql,
                                                 dbname=f'{self.db_name}')
            self.log.info(check_result)
            gsc_size1 = int(check_result.splitlines()[-2].strip())
            self.assertGreater(gsc_size1, gsc_size2, '执行失败:' + step_txt)
            gsc_size2 = gsc_size1
            recheck_msg = self.recheck_gsc()
            self.assertIn(self.tb_ex_name, recheck_msg, '执行失败:' + step_txt)

        step_txt = '----step7:表已缓存时，查看syscache占用情况; ' \
                   'expect:系统缓存不增加或者增加，增加系统缓存信息不含已缓存的用户表----'
        self.log.info(step_txt)
        for i in range(self.tb_num):
            self.save_tmp_info()
            check_sql = f"select * from {self.tb_ex_name}{i};" \
                f"select * from gs_gsc_dbstat_info() where " \
                f"database_name = '{self.db_name}';" \
                f"select usedsize from gs_gsc_memory_detail " \
                f"where db_id = (select cast((select oid from " \
                f"pg_database where datname = '{self.db_name}') as text));"
            check_result = self.sh.execut_db_sql(check_sql,
                                                 dbname=f'{self.db_name}')
            self.log.info(check_result)
            gsc_size1 = int(check_result.splitlines()[-2].strip())
            if gsc_size1 > gsc_size2:
                recheck_msg = self.recheck_gsc()
                sql = f"select * from gs_gsc_table_detail() " \
                    f"where database_name = '{self.db_name}';"
                msg = self.sh.execut_db_sql(sql,
                                            dbname=f'{self.db_name}')
                self.log.info(msg)
                self.assertNotIn(self.tb_ex_name, recheck_msg,
                                 '执行失败:' + step_txt)
                self.assertLess(recheck_msg.count("(0 rows)"), 2,
                                '执行失败:' + step_txt)
                gsc_size2 = gsc_size1
            self.assertEqual(gsc_size1, gsc_size2, '执行失败:' + step_txt)

    def save_tmp_info(self):
        """
        保存gs_gsc_catalog_detail()以及gs_gsc_table_detail()缓存信息
        :return: 返回插入情况
        """
        tmp_sql = f"delete from {self.tb_tmp_catalog} ;" \
            f"insert into {self.tb_tmp_catalog} " \
            f"select database_id,database_name,rel_id,rel_name," \
            f"cache_id,self,infomask,infomask2,hash_value," \
            f"refcount from gs_gsc_catalog_detail() " \
            f"where database_name = '{self.db_name}';" \
            f"delete from {self.tb_tmp_table};" \
            f"insert into {self.tb_tmp_table} " \
            f"select * from gs_gsc_table_detail() " \
            f"where database_name = '{self.db_name}';"
        tmp_result = self.sh.execut_db_sql(tmp_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(tmp_result)
        return tmp_result

    def recheck_gsc(self):
        """
        比对最新系统缓存信息与历史缓存记录比对，返回新增的系统缓存项
        :return:
        """
        recheck_sql = f"(select database_id,database_name,rel_id," \
            f"rel_name,cache_id,self,infomask,infomask2,hash_value," \
            f"refcount from gs_gsc_catalog_detail() " \
            f"where database_name = '{self.db_name}') except " \
            f"(select * from {self.tb_tmp_catalog});" \
            f"(select * from gs_gsc_table_detail() " \
            f"where database_name = '{self.db_name}') except " \
            f"(select * from {self.tb_tmp_table});"
        recheck_result = self.sh.execut_db_sql(recheck_sql,
                                               dbname=f'{self.db_name}')
        self.log.info(recheck_result)
        return recheck_result

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step1_txt = '----删除数据库 expect:删除成功----'
        self.log.info(step1_txt)
        drop_db = f'drop database {self.db_name}'
        drop_db_result = self.sh.execut_db_sql(drop_db)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = self.sh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"enable_global_syscache "
                                              f"={self.init_para1}")
            self.msg2 = self.sh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"enable_thread_pool "
                                              f"={self.init_para2}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = self.sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            self.status_result = self.sh.get_db_cluster_status('status')

        self.log.info(f'----{os.path.basename(__file__)}:end----')
        flag = self.constant.DROP_DATABASE_SUCCESS
        self.assertIn(flag, drop_db_result, '执行失败:' + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + step_txt + self.step2_txt)
            self.assertTrue(self.msg2, '执行失败:' + step_txt + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + step_txt + self.step3_txt)
            self.assertTrue(self.status_result,
                            '执行失败:' + step_txt + self.step4_txt)
